USE [Weval test]
GO

/****** Object:  StoredProcedure [dbo].[ScoreByQuestionAllClasses]    Script Date: 7/8/2013 7:23:09 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[ScoreByQuestionAllClasses] @InstructorID int
AS

SELECT 
	CONVERT(nvarchar(400),Q.text) Question,
	AVG(CASE WHEN CAST(D.text AS nvarchar(255)) = 'strongly disagree' THEN 0.0 
		 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'disagree' THEN 1.0
		 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'neutral' THEN 2.0
		 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'agree' THEN 3.0
		 WHEN LOWER(CAST(D.text AS nvarchar(255))) = 'strongly agree' THEN 4.0
		 END) AS score,
	ST.instructorID,
	(UI.FirstName + ' ' + UI.LastName) AS Instructor,
	CS.bannerCRN,
	CS.semester,
	CS.year,
	CS.block,
	CS.courseNumber,
	CS.subjectCode

FROM SectionsTaught ST
	INNER JOIN Results R ON R.sectionID = ST.sectionID
		AND ST.instructorID = @InstructorID
		AND (R.deleted = 0 OR R.deleted IS NULL)
		AND R.startTime BETWEEN DATEADD(year, -5, GETDATE()) AND GETDATE() --Last 5 years
		--AND ST.instructorID = 887969243
	INNER JOIN CourseSections CS ON CS.ID = R.sectionID
		AND CS.subjectCode = 'CS'
	INNER JOIN QuestionResponses QR ON QR.resultID = R.ID
	INNER JOIN Questions Q ON Q.ID = QR.questionID
	INNER JOIN Tests T ON T.ID = Q.testID 
		AND T.typeID = 5 --Course Eval
	INNER JOIN DistractorResponses DR ON DR.questionResponseID = QR.ID
	INNER JOIN Distractors D ON D.ID = DR.distractorID
	--INNER JOIN Users U ON U.Id = R.studentID
	--INNER JOIN Deliveries DEL ON DEL.ID = R.deliveryID
	
	INNER JOIN Users UI ON UI.Id = ST.instructorID

GROUP BY CONVERT(nvarchar(400),Q.text), 
	ST.instructorID, 
	(UI.FirstName + ' ' + UI.LastName),
	CS.bannerCRN,
	CS.semester,
	CS.year,
	CS.block,
	CS.courseNumber,
	CS.subjectCode


GO

